﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Web;
using Eacademy.Domain.Abstractions;
using Eacademy.Domain.Entities;
using Eacademy.Domain.Models;
using Eacademy.Domain.ViewModels;
using Eacademy.Library.Helpers;
using Microsoft.Practices.EnterpriseLibrary.Data;
using SubSonic.DataProviders;
using Elmah;

namespace Eacademy.Domain.Repository
{
    public class QuizRepository : IQuizRepository
    {
        EacademyDB db = new EacademyDB();

        public Boolean AddQuiz(QuizViewModel quizViewModel, int subjectId, int mentorUserId)
        {
            try
            {
                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Quiz quiz = new Quiz();
                        quiz.Description = quizViewModel.Description;
                        quiz.TimeLimit = quizViewModel.TimeLimit;
                        quiz.PassingScore = quizViewModel.PassingScore;
                        quiz.TotalItems = quizViewModel.TotalItems;
                        quiz.Save();

                        Mentor mentor = db.Select.From<Mentor>().Where(MentorsTable.UserIdColumn)
                            .IsEqualTo(mentorUserId).ExecuteSingle<Mentor>();

                        QuizSubject quizSubject = new QuizSubject();
                        quizSubject.SubjectId = subjectId;
                        quizSubject.MentorId = mentor.MentorId;
                        quizSubject.QuizId = quiz.QuizId;
                        quizSubject.Save();

                    }
                    ts.Complete();
                }

                return true;
            }
            catch (Exception ex)
            { }
            return false;
        }

        public Boolean AddQuestion(QuestionViewModel questionViewModel)
        {


            try
            {
                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Question question = new Question();
                        question.QuizId = questionViewModel.QuizId;
                        question.Question = questionViewModel.Question;
                        question.Save();

                        foreach (ChoiceViewModel c in questionViewModel.Choices)
                        {
                            if (!String.IsNullOrEmpty(c.ChoiceAnswer))
                            {
                                Choice choice = new Choice();
                                choice.QuestionId = question.QuestionId;
                                choice.Choice = c.ChoiceAnswer;
                                choice.IsAnswer = c.IsAnswer;
                                choice.Save();
                                if (c.IsAnswer)
                                {
                                    question.SetIsNew(false);
                                    question.SetIsLoaded(true);
                                    question.SetKeyValue(question.QuestionId);
                                    question.ChoiceIdAnswer = choice.ChoiceId;
                                    question.Save();
                                }
                            }
                        }

                    }
                    ts.Complete();
                }

                return true;
            }
            catch (Exception ex)
            { }
            return false;
        }

        public List<QuizViewModel> GetQuizzesBySubjectId(int subjectId, int mentorUserId, int pageIndex, int pageSize, out int totalCount, bool count = true)
        {
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<QuizViewModel> lessonViewList = new List<QuizViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT q.*,");
                sql.AppendLine(@"       qs.subject_id,");
                sql.AppendLine(@"       qs.mentor_id,");
                sql.AppendLine(@"       qs.quiz_subject_id");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM   quiz_subjects qs");
                sql.AppendLine(@"       LEFT JOIN quiz q");
                sql.AppendLine(@"         ON q.quiz_id = qs.quiz_id");
                sql.AppendLine(@"WHERE  1 = 1");
                sql.AppendLine(@"       AND qs.subject_id = ?SubjectId");
                sql.AppendLine(@"       AND qs.mentor_id = (SELECT m.mentor_id");
                sql.AppendLine(@"                           FROM   mentors m");
                sql.AppendLine(@"                           WHERE  m.user_id = ?MentorUserId)  ");


                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?SubjectId", DbType.Int32, subjectId);
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                db.AddInParameter(comm, "?MentorUserId", DbType.Int32, mentorUserId);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                lessonViewList = studentsTable.AsEnumerable().Select(datarow => new QuizViewModel()
                {
                    TimeLimit = datarow["time_limit"].ToInteger(),
                    DatePosted = datarow["date_posted"].ToDate(),
                    PassingScore = datarow["passing_score"].ToInteger(),
                    Description = datarow["description"].ToString(),
                    TotalItems = datarow["total_items"].ToInteger(),
                    QuizId = datarow["quiz_id"].ToInteger()

                }).ToList();

                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }


                return lessonViewList;
            }
            catch (Exception ex)
            { }
            return lessonViewList;
        }

        public List<QuizViewModel> GetStudentQuizzesBySubjectId(int subjectId, int mentorUserId, int pageIndex, int pageSize, out int totalCount, bool count = true)
        {
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<QuizViewModel> lessonViewList = new List<QuizViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT q.*,");
                sql.AppendLine(@"       qs.subject_id,");
                sql.AppendLine(@"       qs.mentor_id,");
                sql.AppendLine(@"       qs.quiz_subject_id,");
                sql.AppendLine(@"       COALESCE(qst.done,0) as done,");
                sql.AppendLine(@"       COALESCE(qst.date_started,'0001-01-01 00:00:00') as date_started,");
                sql.AppendLine(@"       COALESCE(qst.date_end,'0001-01-01 00:00:00') as date_end,");
                sql.AppendLine(@"       (SELECT COUNT(*) FROM questions WHERE quiz_id = q.quiz_id) as questions");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM   quiz_subjects qs");
                sql.AppendLine(@"       LEFT JOIN quiz q");
                sql.AppendLine(@"         ON q.quiz_id = qs.quiz_id");
                sql.AppendLine(@"       LEFT JOIN quiz_students qst");
                sql.AppendLine(@"         ON qst.quiz_id = q.quiz_id");
                sql.AppendLine(@"       LEFT JOIN students  st");
                sql.AppendLine(@"         ON st.student_id = qst.student_id");
                sql.AppendLine(@"WHERE  1 = 1");
                sql.AppendLine(@"       AND qs.subject_id = ?SubjectId");
                sql.AppendLine(@"       AND qs.mentor_id = (SELECT m.mentor_id");
                sql.AppendLine(@"                           FROM   mentors m");
                sql.AppendLine(@"                           WHERE  m.user_id = ?MentorUserId)");


                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?SubjectId", DbType.Int32, subjectId);
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                db.AddInParameter(comm, "?MentorUserId", DbType.Int32, mentorUserId);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                lessonViewList = studentsTable.AsEnumerable().Select(datarow => new QuizViewModel()
                {
                    TimeLimit = datarow["time_limit"].ToInteger(),
                    DatePosted = datarow["date_posted"].ToDate(),
                    PassingScore = datarow["passing_score"].ToInteger(),
                    Description = datarow["description"].ToString(),
                    TotalItems = datarow["total_items"].ToInteger(),
                    QuizId = datarow["quiz_id"].ToInteger(),
                    DateStarted = datarow["date_started"].ToDate(),
                    DateEnd = datarow["date_end"].ToDate(),
                    Disabled = (datarow["done"].ToBooleaan() || datarow["questions"].ToInteger() < 1) ? true : false
                }).ToList();

                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }


                return lessonViewList;
            }
            catch (Exception ex)
            { }
            return lessonViewList;
        }

        public List<QuestionViewModel> GetQuestionsByQuizId(int quizId)
        {
            int totalCount = 0;
            return GetQuestionsByQuizId(quizId, 0, 0, out totalCount);
        }

        public List<QuestionViewModel> GetQuestionsByQuizId(int quizId, int pageIndex, int pageSize, out int totalCount, bool count = true)
        {
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<QuestionViewModel> questionViewList = new List<QuestionViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT *");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM   questions q");
                sql.AppendLine(@"WHERE  1 = 1");
                sql.AppendLine(@"       AND q.quiz_id = ?QuizId  ");



                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?QuizId", DbType.Int32, quizId);
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable questionsTable = ds.Tables[0];


                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }

                //Use my extension method to return event_id 's as comma delimited string
                String questionIds = questionsTable.ToDelimitedString("question_id");

                sql = new StringBuilder();
                sql.AppendLine(@"SELECT * ");
                sql.AppendLine(@"FROM   choices c");
                sql.AppendFormat(@"WHERE  c.question_id IN ({0})", questionIds);

                comm.CommandText = sql.ToString();
                ds = factory.ExecuteQuery(comm);

                DataTable questionChoicesTable = ds.Tables[0];

                foreach (DataRow datarow in questionsTable.Rows)
                {
                    var questionObject = new QuestionViewModel
                                             {
                                                 Answer = datarow["answer"].ToInteger(),
                                                 Question = datarow["question"].ToString(),
                                                 QuestionType = datarow["question_type"].ToInteger(),
                                                 QuestionId = datarow["question_id"].ToInteger(),
                                                 ChoiceIdAnswer = datarow["choice_id_answer"].ToInteger()
                                             };

                    var choices = questionChoicesTable.AsEnumerable().Where(
                        x => x["question_id"].ToInteger() == questionObject.QuestionId)
                        .Select(choiceRow => new ChoiceViewModel
                                {
                                    ChoiceAnswer = choiceRow["choice"].ToString(),
                                    ChoiceId = choiceRow["choice_id"].ToInteger(),
                                    IsAnswer = choiceRow["is_answer"].ToInteger() == 1 ? true : false,
                                    QuestionId = choiceRow["question_id"].ToInteger()
                                }).ToList();

                    //foreach (ChoiceViewModel choiceViewModel in choices)
                    //{
                    //    if (choiceViewModel.IsAnswer)
                    //    {
                    //        questionObject.ChoiceIdAnswer = choiceViewModel.ChoiceId;
                    //    }
                    //}
                    questionObject.Choices = choices;
                    questionViewList.Add(questionObject);

                }

                return questionViewList;
            }
            catch (Exception ex)
            { }
            return questionViewList;
        }

        public bool DeleteQuiz(int id, int mentorId)
        {
            try
            {
                QuizBase.Delete(x => x.QuizId == id);
                QuizSubjectBase.Delete(x => x.QuizId == id && x.MentorId == mentorId);
                QuestionBase.Delete(x => x.QuizId == id);
                return true;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public bool DeleteQuestion(int id)
        {
            try
            {
                QuestionBase.Delete(x => x.QuestionId == id);
                return true;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public QuizViewModel GetQuizByQuizId(int id)
        {
            QuizViewModel quizViewModel = new QuizViewModel();
            try
            {
                Quiz quiz = db.Select.From<Quiz>().Where(QuizTable.QuizIdColumn).IsEqualTo(id)
                    .ExecuteSingle<Quiz>();

                quizViewModel.Description = quiz.Description;
                quizViewModel.TotalItems = quiz.TotalItems;
                quizViewModel.TimeLimit = quiz.TimeLimit;
                quizViewModel.PassingScore = quiz.PassingScore;

                return quizViewModel;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return null;

        }

        public QuestionViewModel GetQuestionByQuestionId(int id)
        {
            QuestionViewModel questionViewModel = new QuestionViewModel();
            try
            {

                Question question = db.Select.From<Question>().Where(QuestionsTable.QuestionIdColumn)
                    .IsEqualTo(id).ExecuteSingle<Question>();

                var choices = db.Select.From<Choice>().Where(ChoicesTable.QuestionIdColumn)
                    .IsEqualTo(id).ExecuteTypedList<Choice>();

                questionViewModel.Question = question.Question;
                questionViewModel.QuestionId = question.QuestionId;
                questionViewModel.QuizId = question.QuizId;
                questionViewModel.ChoiceIdAnswer = question.ChoiceIdAnswer;

                var choiceList = choices.AsEnumerable().Select(x => new ChoiceViewModel
                                {
                                    ChoiceAnswer = x.Choice,
                                    ChoiceId = x.ChoiceId,
                                    IsAnswer = x.IsAnswer
                                }).ToList();

                questionViewModel.Choices = choiceList;

                return questionViewModel;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return null;

        }

        public Boolean UpdateQuiz(QuizViewModel quizViewModel)
        {
            try
            {
                Quiz quiz = db.Select.From<Quiz>().Where(QuizTable.QuizIdColumn).IsEqualTo(quizViewModel.QuizId)
                    .ExecuteSingle<Quiz>();
                quiz.PassingScore = quizViewModel.PassingScore;
                quiz.TotalItems = quizViewModel.TotalItems;
                quiz.TimeLimit = quizViewModel.TimeLimit;
                quiz.Description = quizViewModel.Description;
                quiz.Save();

                return true;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;

        }

        public Boolean UpdateQuestion(QuestionViewModel questionViewModel)
        {
            try
            {
                Question question = db.Select.From<Question>().Where(QuestionsTable.QuestionIdColumn)
                    .IsEqualTo(questionViewModel.QuestionId).ExecuteSingle<Question>();
                question.ChoiceIdAnswer = questionViewModel.ChoiceIdAnswer;
                question.Question = questionViewModel.Question;
                question.QuizId = questionViewModel.QuizId;


                foreach (ChoiceViewModel c in questionViewModel.Choices)
                {
                    Choice choice = db.Select.From<Choice>().Where(ChoicesTable.ChoiceIdColumn)
                        .IsEqualTo(c.ChoiceId).ExecuteSingle<Choice>();
                    choice.IsAnswer = c.IsAnswer;
                    choice.Choice = c.ChoiceAnswer;
                    if (c.IsAnswer)
                    {
                        question.ChoiceIdAnswer = choice.ChoiceId;
                        question.Save();
                    }
                }
                return true;
            }
            catch (Exception ex)
            {

                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public QuizStudent AddQuizStudent(QuizStudent quizStudent)
        {
            try
            {
                quizStudent.Save();
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return quizStudent;
        }


        public QuizStudent GetQuizStudent(int quizId)
        {
            QuizStudent quizStudent = new QuizStudent();
            try
            {
                quizStudent = db.Select.From<QuizStudent>().Where(QuizStudentsTable.QuizIdColumn)
                    .IsEqualTo(quizId).ExecuteSingle<QuizStudent>();

            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return quizStudent;
        }

        public Boolean UpdateQuizStudentScore(int quizStudentId, int score)
        {
            try
            {
                QuizStudent quizStudent = db.Select.From<QuizStudent>().Where(QuizStudentsTable.QuizStudentIdColumn)
                    .IsEqualTo(quizStudentId).ExecuteSingle<QuizStudent>();
                quizStudent.Score = score;
                quizStudent.Done = true.ToInteger();
                quizStudent.Save();
                return true;
            }
            catch (Exception ex)
            {

                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public Boolean UpdateQuizStudentDone(int quizStudentId)
        {
            try
            {
                QuizStudent quizStudent = db.Select.From<QuizStudent>().Where(QuizStudentsTable.QuizStudentIdColumn)
                    .IsEqualTo(quizStudentId).ExecuteSingle<QuizStudent>();
                quizStudent.Done = true.ToInteger();
                quizStudent.Save();
                return true;
            }
            catch (Exception ex)
            {

                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public QuestionsAnswer AddQuestionAsnwer(QuestionsAnswer questionsAnswer)
        {

            try
            {
                var questionAnswerCheck = db.Select.From<QuestionsAnswer>()
                    .Where(QuestionsAnswersTable.QuestionIdColumn).IsEqualTo(questionsAnswer.QuestionId)
                    .And(QuestionsAnswersTable.QuizStudentIdColumn).IsEqualTo(questionsAnswer.QuizStudentId)
                    .ExecuteSingle<QuestionsAnswer>();
                if (questionAnswerCheck != null)
                {
                    questionAnswerCheck.ChoiceAnswerId = questionsAnswer.ChoiceAnswerId;
                    questionAnswerCheck.Save();
                }
                else
                {
                    questionsAnswer.Save();
                }

            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return questionsAnswer;
        }

        public QuestionsAnswer GetQuestionAnswer(int questionId, int quizStudentId)
        {
            QuestionsAnswer questionsAnswer = new QuestionsAnswer();
            try
            {
                questionsAnswer = db.Select.From<QuestionsAnswer>()
                    .Where(QuestionsAnswersTable.QuestionIdColumn).IsEqualTo(questionId)
                    .And(QuestionsAnswersTable.QuizStudentIdColumn).IsEqualTo(quizStudentId)
                    .ExecuteSingle<QuestionsAnswer>();
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return questionsAnswer;
        }

        public List<SubjectGradeViewModel> GetGradeBookByUserId(int userId)
        {
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<SubjectGradeViewModel> subjectGradeViewModels = new List<SubjectGradeViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine(@"SELECT subject_id,subject_name");
                sql.AppendLine(@"  FROM (SELECT s.subject_id,s.subject_name,");
                sql.AppendLine(@"               (SELECT COUNT(*)");
                sql.AppendLine(@"                  FROM quiz_subjects qs");
                sql.AppendLine(@"                 WHERE qs.subject_id = s.subject_id)");
                sql.AppendLine(@"                  AS quiz_count");
                sql.AppendLine(@"          FROM subjects s) AS tbl");
                sql.AppendLine(@"          WHERE quiz_count > 0");




                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?UserId", DbType.Int32, userId);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable subjectsTable = ds.Tables[0];


                //Use my extension method to return subject_id 's as comma delimited string
                String subjectIds = subjectsTable.ToDelimitedString("subject_id");

                sql = new StringBuilder();
                sql.AppendLine(@"SELECT qs.quiz_id,");
                sql.AppendLine(@"       qs.subject_id,");
                sql.AppendLine(@"       qst.done,");
                sql.AppendLine(@"       s.subject_name,");
                sql.AppendLine(@"       qst.quiz_student_id,");
                sql.AppendLine(@"       qst.score,");
                sql.AppendLine(@"       qst.student_id,");
                sql.AppendLine(@"       st.user_id,");
                sql.AppendLine(@"       qst.total_items,");
                sql.AppendLine(@"       q.description,");
                sql.AppendLine(@"       q.passing_score,");
                sql.AppendLine(@"       q.time_limit,");
                sql.AppendLine(@"       q.total_items AS udf_total_items,");
                sql.AppendLine(@"       (SELECT COUNT(*) FROM questions qq WHERE qq.quiz_id = qs.quiz_id) as total_questions  ");
                sql.AppendLine(@"  FROM subjects s");
                sql.AppendLine(@"       LEFT JOIN quiz_subjects qs");
                sql.AppendLine(@"          ON qs.subject_id = s.subject_id");
                sql.AppendLine(@"       LEFT JOIN quiz_students qst");
                sql.AppendLine(@"          ON qst.quiz_id = qs.quiz_id");
                sql.AppendLine(@"       LEFT JOIN students st");
                sql.AppendLine(@"          ON st.student_id = qst.student_id");
                sql.AppendLine(@"       LEFT JOIN quiz q");
                sql.AppendLine(@"          ON qst.quiz_id = q.quiz_id");
                sql.AppendLine(@" WHERE 1 = 1");
                sql.AppendLine(@" AND st.user_id = ?UserId");
                sql.AppendLine(@" AND qst.done = 1");
                sql.AppendFormat(@"AND  qs.subject_id IN ({0})", subjectIds);

                comm.CommandText = sql.ToString();
                ds = factory.ExecuteQuery(comm);

                DataTable quizStudentsTable = ds.Tables[0];

                foreach (DataRow datarow in subjectsTable.Rows)
                {

                    var gradeBookViewModel = new SubjectGradeViewModel
                    {
                        SubjectName = datarow["subject_name"].ToString(),
                        SubjectId = datarow["subject_id"].ToInteger()
                    };

                    var quizStudentViewModel = quizStudentsTable.AsEnumerable().Where(
                        x => x["subject_id"].ToInteger() == gradeBookViewModel.SubjectId)
                        .Select(quizStudent => new QuizStudentViewModel()
                        {
                            QuizName = quizStudent["description"].ToString(),
                            Score = quizStudent["score"].ToInteger(),
                            TotalItems = quizStudent["total_questions"].ToInteger(),
                            Percentage = (quizStudent["score"].ToInteger() / quizStudent["total_questions"].ToInteger())*50+50
                        }).ToList();


                    gradeBookViewModel.QuizStudentViewModels = quizStudentViewModel;
                    if (quizStudentViewModel.Count > 0)
                    {
                        gradeBookViewModel.TotalItems = quizStudentViewModel.Sum(x => x.TotalItems);
                        gradeBookViewModel.TotalScore = quizStudentViewModel.Sum(x => x.Score).ToInteger();
                        gradeBookViewModel.TotalPercentage =(gradeBookViewModel.TotalScore/gradeBookViewModel.TotalItems).ToInteger()*50 + 50;
                        subjectGradeViewModels.Add(gradeBookViewModel);
                    }


                }

                return subjectGradeViewModels;
            }
            catch (Exception ex)
            { }
            return subjectGradeViewModels;
        }
    }
}